package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import com.hualude.roadsign.model.SignalMachineEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by pjn on 2017/7/22.
 */
public interface SignalMachineRepository extends CrudRepository<SignalMachineEntity, Integer> {


    /**
     * @Description: 信号机产品dt展示
     * @Author: xu_jiaHui
     * @Date: 2017/7/25 15:16
     */
    public List<SignalMachineDto> getSignalMachine4DT(DTRequestDto dtRequestDto,
                                                      String actorIds,
                                                      Integer orgId,
                                                      Boolean audit,
                                                      String startRoad,
                                                      String endRoad,
                                                      Integer alarmBinding) throws Exception;

    /**
     * @Description: 信号机产品dt数量
     * @Author: xu_jiaHui
     * @Date: 2017/7/25 15:29
     */
    public Integer getSignalMachine4DTCount(DTRequestDto dtRequestDto,
                                            String actorIds,
                                            Integer orgId,
                                            Boolean audit,
                                            String startRoad,
                                            String endRoad,
                                            Integer alarmBinding) throws Exception;

    /**
     * @Description: 信号机详情
     * @Author: pan-jianan
     * @Date: 2017/7/22 18:47
     */
    @Query(value = "select new com.hualude.roadsign.dto.SignalMachineFaultDto(sm.signalMachineId,sm.signalMachineLongitude," +
            "sm.signalMachineLatitude,sm.signalMachineProvince,sm.signalMachineCity,sm.signalMachineArea,sm.signalMachineStartRoad," +
            "sm.signalMachineEndRoad,sm.signalMachineType,sm.signalMachineIsAlarm,sm.signalMachinePhoto,sm.signalMachineInstallOrg," +
            "sm.signalMachineCreatorId,sm.signalMachineOrgId,sm.signalMachineMainOrgId) " +
            "from SignalMachineEntity sm where sm.signalMachineId=?1")
    public SignalMachineFaultDto getSignalMachineInfo2Fault(Integer signalMachineId) throws Exception;

    /**
     * @Description: 获取信号机坐标4MAP
     * @Author: pan-jianan
     * @Date: 2017/7/22 15:59
     */
    public List<SignalMachineFaultDto> getMachineCoordinate4Map(Integer orgId, Integer actorId) throws Exception;

    /**
     * @Description: 根据坐标获取信号机信息
     * @Author: pan-jianan
     * @Date: 2017/7/22 22:11
     */
    @Query(value = "select new com.hualude.roadsign.dto.SignalMachineFaultDto(sm.signalMachineId,sm.signalMachineLongitude," +
            "sm.signalMachineLatitude,sm.signalMachineProvince,sm.signalMachineCity,sm.signalMachineArea,sm.signalMachineStartRoad," +
            "sm.signalMachineEndRoad,sm.signalMachineType,sm.signalMachineIsAlarm,sm.signalMachinePhoto,sm.signalMachineInstallOrg," +
            "sm.signalMachineCreatorId,sm.signalMachineOrgId,sm.signalMachineMainOrgId)" +
            "from SignalMachineEntity sm where sm.signalMachineLongitude=?1 and sm.signalMachineLatitude=?2")
    public List<SignalMachineFaultDto> getMachineEnergyByXY(String longitude, String latitude) throws Exception;

    /**
     * @Description: 信号机DT4故障上报
     * @Author: pan-jianan
     * @Date: 2017/7/22 23:26
     */
    public Integer getMachineFaultReportCount4DT(String search, Integer actorId, Integer orgId, String signalMachineStartRoad, String signalMachineEndRoad) throws Exception;

    public List<DTMachineFaultDto> getMachineFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String signalMachineStartRoad, String signalMachineEndRoad) throws Exception;

    /**
     * @Description: 获取第一个路名
     * @Author: pan-jianan
     * @Date: 2017/7/23 0:41
     */
    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 根据第一个获取第二个路名
     * @Author: pan-jianan
     * @Date: 2017/7/23 0:41
     */
    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 删除信号机
     * @Author: xu_jiaHui
     * @Date: 2017/7/26 13:05
     */
    @Modifying
    @Query(value = "delete from SignalMachineEntity where signalMachineId=?1")
    public Integer deleteSignalMachine(Integer signalMachId) throws Exception;

    /**
     * @Description: 编辑信号机
     * @Author: xu_jiaHui
     * @Date: 2017/7/26 13:40
     */
    @Modifying
    @Query(value = "update SignalMachineEntity set projectNumber=?2,signalMachineRegionId=?3,signalMachineLongitude=?4," +
            "signalMachineLatitude=?5,signalMachineProvince=?6,signalMachineCity=?7,signalMachineArea=?8,signalMachineStartRoad=?9," +
            "signalMachineEndRoad=?10,signalMachineType=?11,signalMachineBrand=?12,signalMachineNumber=?13,signalMachineIsAlarm=?14," +
            "signalMachinePhoto=?15,signalMachineInstallTime=?16,signalMachineModifierId=?17,signalMachinePeriod=?18," +
            "signalMachineServiceLife=?19,signalMachineRemark=?20,signalMachineInstallOrg=?21,signalMachineMainOrgId=?22,signalMachineIsComplete=?23" +
            " where  signalMachineId=?1")
    public Integer updateSignalMachine(Integer signalMachineId,
                                       String projectNumber,
                                       Integer signalMachineRegionId,
                                       String signalMachineLongitude,
                                       String signalMachineLatitude,
                                       String signalMachineProvince,
                                       String signalMachineCity,
                                       String signalMachineArea,
                                       String signalMachineStartRoad,
                                       String signalMachineEndRoad,
                                       Integer signalMachineType,
                                       String signalMachineBrand,
                                       String signalMachineNumber,
                                       Integer signalMachineIsAlarm,
                                       String signalMachinePhoto,
                                       String signalMachineInstallTime,
                                       Integer signalMachineModifierId,
                                       Integer signalMachinePeriod,
                                       Integer signalMachineServiceLife,
                                       String signalMachineRemark,
                                       String signalMachineInstallOrg,
                                       Integer signalMachineMainOrgId,
                                       Integer signalMachineIsComplete) throws Exception;

    /**
     * @Description: 获取信号机坐标用于地图显示
     * @Author: xu_jiaHui
     * @Date: 2017/7/27 13:41
     */
    public List<SignalMachineDto> getSignalMachineCoordinate4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception;


    /**
     * @Description: 信号机信息展示4app
     * @Author: xu_jiaHui
     * @Date: 2017/7/27 15:31
     */
    public List<SignalMachineDto> getSignalMachine4APP(
            String actorIds, Integer orgId, Integer mCount,
            Integer mSize, String city, String startRoad, String endRoad, String alarmType) throws Exception;


    /**
     * @Description: 通过id获取信号机信息
     * @Author: xu_jiaHui
     * @Date: 2017/7/27 15:32
     */
    public List<SignalMachineDto> getSignalMachineById(Integer signalMachineById) throws Exception;

    /**
     * @Description: 通过坐标获取信号机信息
     * @author xu_jiaHui
     * @date 2017/7/29 15:31
     */
    @Query(value = "select new com.hualude.roadsign.dto.MapInfoDto(sm.signalMachineId,sm.signalMachinePhoto,sm.projectNumber," +
            "sm.signalMachineProvince,sm.signalMachineCity,sm.signalMachineArea,sm.signalMachineStartRoad," +
            "sm.signalMachineEndRoad,sm.signalMachineType,sm.signalMachineBrand,sm.signalMachineNumber," +
            "sm.signalMachineIsAlarm,sm.signalMachineInstallTime,sm.signalMachineInstallOrg,sm.signalMachinePeriod," +
            "sm.signalMachineServiceLife,sm.signalMachineRemark,u.userName,o1.orgName,o2.orgName) from " +
            "SignalMachineEntity sm,OrgEntity o1,OrgEntity o2,UserEntity u where o1.orgId=sm.signalMachineOrgId and " +
            "o2.orgId=sm.signalMachineMainOrgId and sm.signalMachineCreatorId=u.userId and sm.signalMachineLongitude=?1 " +
            "and sm.signalMachineLatitude=?2")
    public List<MapInfoDto> getSignalMachineInfoByXY(String solarEnergyLongitude, String solarEnergyLatitude) throws Exception;


    /**
     * @Description: 根据OrgId，actorId获取城市4App
     * @author xu_jiaHui
     * @date 2017/7/30 15:17
     */
    public List<String> getCity4SignalMachine(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 根据city获取路口4App
     * @author xu_jiaHui
     * @date 2017/7/30 15:24
     */
    public List<String> getStartRoadByCity(String city, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 获取信号机信息用于App地图展示
     * @author xu_jiaHui
     * @date 2017/8/3 14:11
     */
    public List<SignalMachineDto> getSignalMachine4Map4App(Integer orgId,
                                                           String actorIds,
                                                           double neLongitude,
                                                           double neLatitude,
                                                           double swLongitude,
                                                           double swLatitude) throws Exception;

    /**
     * @Description: 审核信号机信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/12 18:21
     */
    @Modifying
    @Query(value = "update SignalMachineEntity set signalMachineIsComplete=1 where signalMachineId=?1")
    public Integer auditSignalMachine(Integer signalMachineId) throws Exception;

    /**
     * @Description: 通过城市 起始路名 获取结束路名4App
     * @author xu_jiaHui
     * @date 2017/8/14 20:44
     */
    public List<String> getSectionByStartRoad4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;


    @Modifying
    @Query(value = "update SignalMachineEntity set alarmDeviceId=?1 where signalMachineId=?2 and alarmDeviceId=null")
    public Integer bindingDevice(String deviceId, Integer signalMachineId) throws Exception;

    @Modifying
    @Query(value = "update SignalMachineEntity set alarmDeviceId=null where signalMachineId=?1")
    public Integer unbindingDevice(Integer signalMachineId) throws Exception;

    public List<SignalMachineDto> getDeviceInfo(Integer deviceId) throws Exception;

}
